
-第一题
CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
 )

--第二题

CREATE TABLE Employees
(
   Id INT PRIMARY KEY NOT NULL identity,
   Name nvarchar(80) NOT NULL,
   Age INT NOT NULL,
   Address NVARCHAR(50),
   SALARY decimal(18,2)
);

CREATE TABLE AuditLog
(
   Id int primary key not null identity,
   NAME TEXT NOT NULL,
   Salary decimal(18,2)
);


INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );
select * from AuditLog;
--Paul | 20000.00

select * from Employees


go
create trigger tr_trg_AuditLog
on Employees
instead of insert
as
begin
	declare @count int,@salary int 

	select @count=COUNT(*) from inserted

	if(@count>1)
		begin
			print  '����һ���͹���'
		end
	else
		begin
			select @salary=SALARY from inserted
			insert into AuditLog (NAME,Salary) select * from Employees where @salary=SALARY
		end
end
go

select * from AuditLog

--第三题
go
CREATE TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
)
��̨�����2������:
insert into Author (FirstName,LastName,UpdatedTime) values ('PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('NICK', 'WAHLBERG', '2006-02-15 12:34:33');

--第四题

CREATE TABLE Employees1111
(
	Y int ,
   Id int primary key  identity,
   EmployeeCode nvarchar(80),
   Birthday date ,
   FirstName nvarchar(14) ,
   LastName nvarchar(16) ,
   Gender char(1) ,
   HireDate date ,
   parentId int 
);

	insert into Employees1111 (Y) values (1)
insert into Employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId) values ('10001','1953-09-02','Georgi','Facello','M','1986-06-26',(select top 1 Y from Employees1111 where Y = 1))
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10002','1964-06-02','Bezalel','Simmel','F','1985-11-21',(select top 1 Y from Employees1111 where Y = 1));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10003','1959-12-03','Parto','Bamford','M','1986-08-28',(select top 1 Y from Employees1111 where Y = 1));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10004','1954-05-01','Chirstian','Koblick','M','1986-12-01',(select top 1 Y from Employees1111 where Y = 1));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10005','1955-01-21','Kyoichi','Maliniak','M','1989-09-12',(select top 1 Y from Employees1111 where Y = 1));
	insert into Employees1111 (Y) values (2)
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10006','1953-04-20','Anneke','Preusig','F','1989-06-02',(select top 1 Y from Employees1111 where Y = 2));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10007','1957-05-23','Tzvetan','Zielinski','F','1989-02-10',(select top 1 Y from Employees1111 where Y = 2));
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10008','1958-02-19','Saniya','Kalloufi','M','1994-09-15',(select top 1 Y from Employees1111 where Y = 2));
INSERT INTO employees1111(EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10009','1952-04-19','Sumant','Peac','F','1985-02-18',(select top 1 Y from Employees1111 where Y = 2));
INSERT INTO employees1111(EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10010','1963-06-01','Duangkaew','Piveteau','F','1989-08-24',(select top 1 Y from Employees1111 where Y = 2));
	insert into Employees1111 (Y) values (3)
INSERT INTO employees1111 (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId)  VALUES('10011','1953-11-07','Mary','Sluis','F','1990-01-22',(select top 1 Y from Employees1111 where Y = 3))

select * from Employees1111

;with CTE_A
as
(
	select Id ,EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate,parentId,0 level from Employees1111 where id = 1
	union all
	select a.Id ,a.EmployeeCode,a.Birthday,a.FirstName,a.LastName,a.Gender,a.HireDate,a.parentId,1+level level from Employees1111 a , CTE_A b
	where a.Id =b.parentId

)select * from CTE_A

--第五题

create proc proc_a
@res nvarchar(80)
as
begin
	declare @acceptlog nvarchar(80),@date date
	select @acceptlog=acceptlog,@date =date
		if (@acceptlog=1)
			begin
				set @res ='������'
			end
	else
		begin
			set @res ='������'
		end

		if (@date>date)
			begin
				set @res ='��ɾ��'
			end
end





